package team.mutong.taxi.jdbc;

import java.io.ObjectInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import team.mutong.taxi.domain.DtwFeature;
import team.mutong.taxi.domain.GPSPoint;
import team.mutong.taxi.utils.ChangeType;

/** 数据库操作类 */
public class DataBase {
	static Connection con = null;
	static PreparedStatement pstmt = null;
	static Statement stmt = null;
	static ResultSet rs = null;
	static String sql = null;

	/** 连接数据库 */
	public static void connectDB() {
		try {
			System.out.println("正在连接数据库...");
			Class.forName("com.mysql.jdbc.Driver").newInstance(); // 创建MySQL数据库类实例
			con = DriverManager
					.getConnection(
							"jdbc:mysql://172.23.21.26:3306/hwb?useUnicode=true&characterEncoding=utf-8",
							"root", "123456");
			System.out.println("连接成功！");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/** 关闭连接 */
	public static void closeDB() {
		System.out.println("正在关闭数据库...");
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
			System.out.println("数据库连接已关闭！");

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 得到tomwang_test表中的所有数据
	 * 
	 * @return
	 * @author TomWang
	 */
	public static List<Map<String, Object>> getTestSet(String tableName) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select * from " + tableName;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
<<<<<<< HEAD
	 * 得到tomwang_savedtrainresult510表中的所有数据
=======
	 * 得到tomwang_savedtestresult33表中的所有数据
>>>>>>> 2c7179eb9afb7632bcc3034d42e7edd52e34e05e
	 * 
	 * @return
	 * @author TomWang
	 */
	public static List<Map<String, Object>> get_savedTestResult(String tableName) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select * from " + tableName;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 得到tomwang_test表中的对应的数据
	 * 
	 * @return
	 * @author TomWang
	 */
	@SuppressWarnings("unchecked")
	public static List<DtwFeature> getTestTrace() {
		List<DtwFeature> list = new ArrayList<>();
		String sql = "select TRIP_ID from tomwang_savedtestresult520";
		try {
			pstmt = con.prepareStatement(sql);
			// pstmt.setString(1, string);
			rs = pstmt.executeQuery();
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				List<GPSPoint> cutRoad = new ArrayList<>();
				for (int i = 1; i <= columnCount; i = i + 2) {
					GPSPoint xGpsPoint = new GPSPoint(rs.getDouble(i),
							rs.getDouble(i + 1));
					cutRoad.add(xGpsPoint);
				}
				DtwFeature rowData = new DtwFeature(null, cutRoad, 0, 0);
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 得到某个表中的数据个数
	 * 
	 * @param tableName
	 *            表名
	 * @return 个数int
	 * @author TomWang
	 */
	public static int getToteCount(String tableName) {
		int length = 0;
		String sql = "select count(*) from " + tableName;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			length = rs.getInt("count(*)");
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return length;
	}

	/**
	 * 分页查询表TomWang_train
	 * 
	 * @param beginIndex
	 *            起始位置
	 * @param pageCount
	 *            页面大小
	 * @return
	 * @author TomWang
	 */
	public static List<Map<String, Object>> getTrainSet(String tableName,
			int beginIndex, int pageCount) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String sql = "select * from " + tableName + " limit " + beginIndex
				+ "," + pageCount;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 保存匹配结果
	 * 
	 * @param dTWresult
	 */
	public static void addDTWresult(List<DtwFeature> dTWresult, String tripId) {
		String SQL = "INSERT INTO tomwang_savedtestresult520(TRIP_ID, VALUE) VALUES (?, ?)";
		try {
			pstmt = con.prepareStatement(SQL);
			pstmt.setString(1, tripId);
			pstmt.setObject(2, dTWresult);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * 从数据库中取出数据
	 * 
	 * @param string
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<DtwFeature> getDTWresult(String string) {
		List<DtwFeature> result = new ArrayList<>();
		String Sql = "select VALUE from tomwang_savedtestresult520 where TRIP_ID = ?";
		try {
			pstmt = con.prepareStatement(Sql);
			pstmt.setString(1, string);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				ObjectInputStream oips = new ObjectInputStream(
						rs.getBinaryStream(1));
				// 从rs中得到对象的流,如果直接从rs.getObject(1)得到的对象是无法直接转化为下面的对象的。
				result = (List<DtwFeature>) oips.readObject();// 从流中读取对象
				oips.close();
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static void addTestUse(List<Map<String, Object>> testSet,
			String tableName) {
		String SQL = "INSERT INTO "
				+ tableName
				+ " (TRIP_ID,CALL_TYPE,ORIGIN_CALL,ORIGIN_STAND,TAXI_ID,TIMESTAMP,DAY_TYPE,MISSING_DATA,POLYLINE) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = con.prepareStatement(SQL);
			for (Map<String, Object> temp : testSet) {
				pstmt.setString(1, (String) temp.get("TRIP_ID"));
				pstmt.setString(2, (String) temp.get("CALL_TYPE"));
				pstmt.setString(3, (String) temp.get("ORIGIN_CALL"));
				pstmt.setString(4, (String) temp.get("ORIGIN_STAND"));
				pstmt.setString(5, (String) temp.get("TAXI_ID"));
				pstmt.setString(6, (String) temp.get("TIMESTAMP"));
				pstmt.setString(7, (String) temp.get("DAY_TYPE"));
				pstmt.setString(8, (String) temp.get("MISSING_DATA"));
				String trainSetRoad = ChangeType
						.blobChangetoString((byte[]) temp.get("POLYLINE"));
				List<GPSPoint> trainSetGpsPoint = ChangeType
						.changeToObject(trainSetRoad);// 将坐标转换成二维数组
				int length = trainSetGpsPoint.size();
				for (int i = length - 1; i > length / 2; i--) {
					trainSetGpsPoint.remove(i);
				}
				pstmt.setString(9, trainSetGpsPoint.toString().replace(" ", ""));
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * 根据TRIP_ID从测试集中得到DTWFeature
	 * 
	 * @param tableName
	 *            数据表的名字
	 * @param tripId
	 *            行程ID
	 * @author tomwang
	 */
	public static List<DtwFeature> getTestFeatureByTripId(String tableName,
			String tripId) {
		List<DtwFeature> result = new ArrayList<>();
		String sql = "select * from " + tableName + " where TRIP_ID = ?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, tripId);
			rs = pstmt.executeQuery();
			// 生成ResultSetMetaData
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			while (rs.next()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(rsMeta.getColumnName(i), rs.getObject(i));
				}
				result.add(new DtwFeature(rowData));
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 得到所有的测试集的TRIP_ID
	 * 
	 * @param tableName
	 *            数据表的名字
	 * @author tomwang
	 */
	public static List<String> getAllTripId(String tableName) {
		List<String> result = new ArrayList<>();
		String sql = "select TRIP_ID from " + tableName;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(rs.getString("TRIP_ID"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException:" + e.getMessage());
			System.out.println("SQLState:" + e.getSQLState());
			System.out.println("VendorError:" + e.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
